package cglgu.trellis.chado.das2.cglgu.db;

/*
 * $Id: CglguDbUtil.java 191 2011-03-27 00:38:22Z sundaram.georgetown@gmail.com $
 * $Author: sundaram.georgetown@gmail.com $
 * $Date: 2011-03-27 00:38:22 +0000 (Sun, 27 Mar 2011) $
 * $Rev: 191 $
 * $HeadURL: http://genomancer.googlecode.com/svn/branches/gu-dev-branch/tengcha/src/cglgu/trellis/chado/das2/cglgu/db/CglguDbUtil.java $
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.lang.String;
import cglgu.trellis.chado.das2.cglgu.Organism;
import cglgu.trellis.chado.das2.cglgu.Analysis;
import cglgu.trellis.chado.das2.cglgu.Chromosomes;

/* Encapsulates logic for:

   organism: all
   schema: chado
   database vendor: all

   Implements Singleton pattern.
 */

public class CglguDbUtil {

	private String username;
	private String password;
	private String database;
	private String databaseHost;
	private boolean useMultiQuery = false;
	private Connection connection; 

	// Lightweight, intermediate classes for
	// encapsulating these concepts
	private Organism organism;
	private List<Analysis> analysisList;
	private List<Chromosome> chromosomeList;
		
	
	private int analysisCtr=0;
	private int organismCtr=0;
	private int chromosomeCtr=0;
	
	// Table column positions
	private static final short organism_genus_idx = 2;
	private static final short organism_species_idx = 3;
	private static final short analysis_program_idx = 3;
	private static final short analysis_programversion_idx = 4;
	private static final short analysis_description_idx = 2;
	private static final short feature_uniquename_idx = 5;
	private static final short feature_seqlen_idx = 7;
	//
	
	
	// May be more efficient to just deal
	// directly with JDBC ResultSet objects
	private ResultSet analysisRS; 
	private ResultSet chromosomeRS;
	private ResultSet organismRS;

	static String JDBC_MULTIQUERY_PARAM = "?allowMuLtiqueries=true";

	private static CglguDbUtil instance = null;

	protected CglguDbUtil(String uname, String pword, String db, String host, boolean useMultiQry){

		this.username = uname;
		this.password = pword;
		this.database = db;
		this.databaseHost = host;
		this.useMultiQuery = useMultiQry;


		try{
			connection = getConnection();
		} catch (SQLException e){
			System.out.println("Caught some SQLException:");
			e.printStackTrace();
			System.exit(1);
		}

		System.out.println("Established connection with database " + database + " on host " + databaseHost);
	}

	public Connection getConnection() throws SQLException {

		String dbUriRoot = getDbUriRoot();

		String dbUri = dbUriRoot + databaseHost + "/" + database;

		if (useMultiQuery){ 
			dbUri += JDBC_MULTIQUERY_PARAM; 
		}

		Connection conn = DriverManager.getConnection(dbUri, username, password);

		return conn;
	}

	public static CglguDbUtil getInstance(String username, String password, String db, String host, boolean useMultiQry){

		if (instance == null){
			instance = new CglguDbUtil(username, password, db, host, useMultiQry);
		}

		return instance;
	}

	public int getAnalysisCount(){
		if (analysisList == null){
			if (analysisRS == null){
				analysisRS = getAnalysisListRS();
			}
		}
		return analysisCtr;
	}
	
	public ResultSet getAnalysisListRS(){

		if (analysisRS == null){


			/* Retrieve data from analysis table.
			 * Only retrieving analysis records that
			 * are associated with some feature record
			 * where the feature itself is localized to 
			 * some chromosome feature record.
			 * Also, the feature should not be obsolete.
			 */

			String query = "select a.* " +
			"from analysis a, analysisfeature af, feature f, featureloc fl, feature refseq, cvterm c, organism o " +
			"where c.name = 'chromosome' " +
			"and c.cvterm_id = refseq.type_id " +
			"and refseq.organism_id = o.organism_id " +
			"and f.organism_id = o.organism_id " +
			"and refseq.feature_id = fl.srcfeature_id " +
			"and fl.feature_id = f.feature_id " +
			"and f.feature_id = af.feature_id " +
			"and af.analysis_id = a.analysis_id " +
			"and f.is_obsolete = 'f' ";

			try {
				Statement stmt = connection.createStatement();
				analysisRS = stmt.executeQuery(query);
			} catch (SQLException e)  {
				System.err.println("Caught some SQLException error while attempting to execute the following query: " + query);
				e.printStackTrace();
			}
		}
		return analysisRS;
	}

	public List<Analysis> getAnalysisList(){


		if (analysisList == null){
			
			analysisList = new ArrayList<Analysis>;
			
			if (analysisRS == null){
				analysisRS = getAnalysisListRS();
			}

			while (analysisRS.next()){
				String program = analysisRS.getString(analysis_project_idx);
				String description = analysisRS.getString(analysis_description_idx);
				Analysis analysis = new Analysis(project, description);
				analysisList.add(analysis);
				analysisCtr++;
			}
		}

		return analysisList;
	}

	public int getChromosomeCount(){
		if (chromsomeList == null){
			if (chromosomeRS == null){
				chromosomeRS = getChromosomeListRS();
			}
		}
		return chromosomeCtr;
	}

	public ResultSet getChromosomeListRS(){

		/* Retrieve data from feature table.
		 * Only retrieving feature records that
		 * are associated with some analysis.
		 * The features themselves must be localized
		 * to some chromosome.  Only interested in feature
		 * records that are not obsolete.
		 */

		String query = "select refseq.uniquename, refseq.seqlen " +
		"from analysis a, analysisfeature af, feature f, featureloc fl, feature refseq, cvterm c, organism o " +
		"where c.name = 'chromosome' " +
		"and c.cvterm_id = refseq.type_id " +
		"and refseq.organism_id = o.organism_id " +
		"and f.organism_id = o.organism_id " +
		"and refseq.feature_id = fl.srcfeature_id " +
		"and fl.feature_id = f.feature_id " +
		"and f.feature_id = af.feature_id " +
		"and af.analysis_id = a.analysis_id " +
		"and f.is_obsolete = 'f' ";

		try {
			Statement stmt = connection.createStatement();
			chromosomeRS = stmt.executeQuery(query);

		} catch (SQLException e)  {
			System.err.println("Caught some SQLException error while attempting to execute the following query: " + query);
			e.printStackTrace();
		}

		return chromosomeRS;
	}

	public List<Chromosome> getChromosomeList(){

		if (chromosomeList == null){
			
			chromosomeList = new ArrayList<Chromosome>;
			
			if (chromosomeRS == null){
				chromosomeRS = getChromosomeListRS();
			}
			while (chromosomeRS.next()){
				String uniquename = chromosomeRS.getString(feature_uniquename_idx);
				int seqlen = chromosomeRS.getInt(feature_seqlen_idx);
				Chromosome chromosome = new Chromosome(uniquename, seqlen);
				chromosomeList.add(chromosome);
				chromosomeCtr++;
			}
		}

		return chromosomeList;
	}

	public int getorganismCount(){
		if (organismList == null){
			if (organismRS == null){
				organismRS = getorganismListRS();
			}
		}
		return organismCtr;
	}

	public ResultSet getOrganismListRS(){

		/* Retrieve data from organism table.
		 * Only retrieving organism records that
		 * are associated with some feature record
		 * where the feature itself is localized to 
		 * some chromosome feature record.
		 * Also, the feature should not be obsolete.
		 * The feature and the chromosome must be 
		 * associated with the same organism.
		 * The chromosome must be non-obsolete.
		 */

		String query = "select o.* " +
		"from organism o, analysis a, analysisfeature af, feature f, featureloc fl, feature refseq, cvterm c " +
		"where c.name = 'chromosome' " +
		"and c.cvterm_id = refseq.type_id " +
		"and refseq.is_obsolete = 'f' " +
		"and refseq.organism_id = o.organism_id " +
		"and o.organism_id = f.organism_id " +
		"and refseq.feature_id = fl.srcfeature_id " +
		"and fl.feature_id = f.feature_id " +
		"and f.feature_id = af.feature_id " +
		"and af.analysis_id = a.analysis_id " +
		"and f.is_obsolete = 'f' ";

		try {
			Statement stmt = connection.createStatement();
			organismRS = stmt.executeQuery(query);
		} catch (SQLException e)  {
			System.err.println("Caught some SQLException error while attempting to execute the following query: " + query);
			e.printStackTrace();
		}

		return organismRS;
	}
	
	public List<Organism> getOrganismList(){

		if (organismList == null){
	
			organismList = new ArrayList<Organism>;
		
			if (organismRS == null){
				organismRS = getOrganismListRS();
			}
			
			while (organismRS.next()){
				String genus = organismRS.getString(organism_genus_idx);
				String species = organismRS.getString(organism_species_idx);
				Organism organism = new Organism(genus, species);
				organismList.add(organism);
				organismCtr++;
			}
		}
	
		return organismList;
	}
}